import time

import pandas as pd

import 去除企宽

if __name__ == '__main__':
    df = pd.concat([
        去除企宽.get_df('0101_0131', ['所属小区', 'account', 'online_time',
                                      'offline_time', 'olt_ip', 'pon', 'level', 'alarm_name', 'alarm_source',
                                      'additional', 'happen_time', 'clear_time', 'olt_pon']),
        去除企宽.get_df('0201_0228', ['所属小区', 'account', 'online_time',
                                      'offline_time', 'olt_ip', 'pon', 'level', 'alarm_name', 'alarm_source',
                                      'additional', 'happen_time', 'clear_time', 'olt_pon']),
        去除企宽.get_df('0301_0331', ['所属小区', 'account', 'online_time',
                                      'offline_time', 'olt_ip', 'pon', 'level', 'alarm_name', 'alarm_source',
                                      'additional', 'happen_time', 'clear_time', 'olt_pon']),
        去除企宽.get_df('0401_0430', ['所属小区', 'account', 'online_time',
                                      'offline_time', 'olt_ip', 'pon', 'level', 'alarm_name', 'alarm_source',
                                      'additional', 'happen_time', 'clear_time', 'olt_pon']),
        去除企宽.get_df('0501_0531', ['所属小区', 'account', 'online_time',
                                      'offline_time', 'olt_ip', 'pon', 'level', 'alarm_name', 'alarm_source',
                                      'additional', 'happen_time', 'clear_time', 'olt_pon'])
    ])
    t = pd.read_csv("/temp/低满账号.csv", dtype=str)
    t = t.rename(columns={'宽带账号': 'account'})
    t = t.merge(df, on='account')
    t['happen_time'] = pd.to_datetime(t['happen_time'])
    t['clear_time'] = pd.to_datetime(t['clear_time'])
    t['中断时长'] = (t['clear_time'] - t['happen_time']).dt.total_seconds() / 3600

    # 根据账号分组，将每个happen_time用';'分割拼接起来
    grouped_happen_time = t.groupby('account')['happen_time'].apply(
        lambda x: ';'.join(x.dt.strftime('%Y-%m-%d %H:%M:%S'))).reset_index(name='happen_time_joined')
    grouped_clear_time = t.groupby('account')['clear_time'].apply(
        lambda x: ';'.join(x.dt.strftime('%Y-%m-%d %H:%M:%S'))).reset_index(name='clear_time_joined')

    # 只保留日期并拼接
    grouped_date = t.groupby('account')['happen_time'].apply(lambda x: ';'.join(x.dt.strftime('%Y-%m-%d'))).reset_index(
        name='happen_date_joined')

    # 保留到小时并拼接
    grouped_hour = t.groupby('account')['happen_time'].apply(
        lambda x: ';'.join(x.dt.strftime('%Y-%m-%d %H:00'))).reset_index(name='happen_hour_joined')
    grouped_alarm = t.groupby('account')['alarm_name'].apply(lambda x: ';'.join(x)).reset_index(
        name='alarm_name_joined')
    online_time = t.groupby('account')['online_time'].apply(lambda x: ';'.join(x)).reset_index(
        name='上线时间')
    offline_time = t.groupby('account')['offline_time'].apply(lambda x: ';'.join(x)).reset_index(
        name='下线时间')
    # 根据账号分组，将每个 clear_time 往后取整到小时后用 ';' 分割拼接起来
    t['clear_time_rounded'] = t['clear_time'] + pd.Timedelta(hours=1) - pd.to_timedelta(t['clear_time'].dt.minute,
                                                                                        unit='m') - pd.to_timedelta(
        t['clear_time'].dt.second, unit='s')
    grouped_clear_time_hour = t.groupby('account')['clear_time_rounded'].apply(
        lambda x: ';'.join(x.dt.strftime('%Y-%m-%d %H:00'))).reset_index(name='clear_time_hour_joined')

    # 将中间结果按账号连接
    combined_result = pd.merge(grouped_happen_time, grouped_clear_time, on='account')
    combined_result = pd.merge(combined_result, grouped_date, on='account')
    combined_result = pd.merge(combined_result, grouped_hour, on='account')
    combined_result = pd.merge(combined_result, grouped_alarm, on='account')
    combined_result = pd.merge(combined_result, grouped_clear_time_hour, on='account')
    combined_result = pd.merge(combined_result, online_time, on='account')
    combined_result = pd.merge(combined_result, offline_time, on='account')

    # 根据 account 分组统计次数以及中断时长的和
    grouped_stats = t.groupby('account').agg(中断次数=('account', 'count'),
                                             中断时长总和=('中断时长', 'sum')).reset_index()

    # 将统计结果合并到之前的结果中
    combined_result = pd.merge(combined_result, grouped_stats, on='account')

    # 输出到 CSV 文件，包含新的统计结果

    # 筛选 df 中根据 happen_time 和 olt_pon 分组后统计 account 去重后大于 1 的 happen_time 和 olt_pon
    grouped = df.groupby(['happen_time', 'olt_pon'])['account'].nunique().reset_index(name='unique_account_count')
    filtered = grouped[grouped['unique_account_count'] > 1][['happen_time', 'olt_pon']]
    print(filtered)
    filtered['happen_time'] = pd.to_datetime(filtered['happen_time'])

    # 标记同一pon口其他用户一起中断
    result = pd.merge(filtered[['happen_time', 'olt_pon']], t[['happen_time', 'olt_pon', 'account']],
                      on=['happen_time', 'olt_pon'], how='right', indicator=True)
    print(result)
    result = result[result['_merge'] == 'both']

    combined_result['同pon口'] = combined_result['account'].isin(result['account']).map({True: '是', False: '否'})

    output_path = '/temp/中断结果.csv'
    combined_result.to_csv(output_path)

    t = pd.read_csv("/temp/低满账号.csv", dtype=str)
    t = t.rename(columns={'宽带账号': 'account'})

    t = t.merge(combined_result, on='account', how='left')
    t['是否中断'] = t['中断次数'].notnull().map({True: '是', False: '否'})
    # 调整 combined_result 的表头顺序
    new_order = ['account', '是否中断', 'happen_date_joined', '中断时长总和', '中断次数', '同pon口',
                 'happen_time_joined', 'clear_time_joined', 'happen_hour_joined', 'clear_time_hour_joined',
                 'alarm_name_joined', '上线时间', '下线时间']
    t = t[new_order]
    t.to_excel("/temp/t.xlsx", index=False)

    print(f'结果已保存到 {output_path}')
